package com.tianqu.tms.dao;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.tianqu.tms.core.exception.TmsDaoException;
import com.tianqu.tms.dto.CodesDto;

public class CodesDaoImp extends TmsDao implements CodesDao {

	protected CodesDaoImp() throws TmsDaoException {
		super();
	}
	
	
	public List<CodesDto> getCodes(int itemId,String itemValue) throws TmsDaoException{
		PreparedStatement ps = null;
		ResultSet rs = null;
		CodesDto dto = null;
		List<CodesDto> codesList = new ArrayList<CodesDto>();
		
		try {
			conn = super.getConnection();
			
			StringBuffer sql = new StringBuffer();
			sql.append("SELECT ");
			sql.append("ID        	,");
			sql.append("ITEMID    	,");
			sql.append("ITEMCODE  	,");
			sql.append("ITEMVALUE 	 ");
			sql.append("FROM ");
			sql.append(TmsTableNames.TMS_CODES);
			sql.append(" WHERE  ITEMID = '"+itemId+"'");
			sql.append("  AND SEARCHID LIKE '%"+itemValue+"%'");
			sql.append(" ORDER BY ITEMVALUE ASC LIMIT 30");
			ps = conn.prepareStatement(sql.toString());
			rs = ps.executeQuery();

			while (rs.next()) {
				dto = new CodesDto();
				dto.setId(rs.getLong("ID"));
				dto.setItemId(rs.getInt("ITEMID"));
				dto.setItemCode(rs.getInt("ITEMCODE"));
				dto.setItemValue(rs.getString("ITEMVALUE"));
				codesList.add(dto);
			}
		} catch (SQLException e) {
			throw new TmsDaoException(e.toString());
			
		} finally {
			close(rs,ps);
		}
		return codesList;
	}
	
	/**
	 * 获取字典表信息
	 * @throws TmsDaoException 
	 */
	@Override
	public List<CodesDto> instanceCodes() throws TmsDaoException {
		
		PreparedStatement ps = null;
		ResultSet rs = null;
		CodesDto dto = null;
		List<CodesDto> codesList = new ArrayList<CodesDto>();
		
		try {
			conn = super.getConnection();
			
			StringBuffer sql = new StringBuffer();
			sql.append("SELECT ");
			sql.append("ID        	,");
			sql.append("ITEMID    	,");
			sql.append("ITEMCODE  	,");
			sql.append("ITEMVALUE 	 ");
			sql.append("FROM ");
			sql.append(TmsTableNames.TMS_CODES);
			//2013/10/16 liugang add start 
			sql.append("  ORDER BY ITEMID,ITEMCODE ");
			//2013/10/16 liugang add end 
			ps = conn.prepareStatement(sql.toString());
			
			// 执行sql
			rs = ps.executeQuery();

			while (rs.next()) {
				dto = new CodesDto();
				dto.setId(rs.getLong("ID"));
				dto.setItemId(rs.getInt("ITEMID"));
				dto.setItemCode(rs.getInt("ITEMCODE"));
				dto.setItemValue(rs.getString("ITEMVALUE"));

				codesList.add(dto);
			}
		} catch (SQLException e) {
			throw new TmsDaoException(e.toString());
			
		} finally {
			close(rs,ps);
		}
		return codesList;
	}
}
